package cn.sylinx.hbatis.db.dialect.sql;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import cn.sylinx.hbatis.db.mapper.DeleteMapper;
import cn.sylinx.hbatis.db.mapper.InsertMapper;
import cn.sylinx.hbatis.db.mapper.ModelBuilder;
import cn.sylinx.hbatis.db.mapper.UpdateMapper;
import cn.sylinx.hbatis.exception.ExecutingException;
import cn.sylinx.hbatis.ext.model.Model;
import cn.sylinx.hbatis.kit.Pair;
import cn.sylinx.hbatis.kit.StrKit;
import cn.sylinx.hbatis.kit.Tuple;
import cn.sylinx.hbatis.log.GLog;
import cn.sylinx.hbatis.plugin.model.ModelFabric;

public class DefaultSqlBuilder implements SqlBuilder {

	private String escape0 = "";
	private String escape1 = "";

	public DefaultSqlBuilder() {
		String[] escapes = getEscapeChar();
		if (escapes != null && escapes.length >= 2) {
			escape0 = escapes[0];
			escape1 = escapes[1];
		}
	}

	protected String[] getEscapeChar() {
		return new String[] { "", "" };
	}

	@Override
	public Tuple buildPaginatorSql(String preSql, int pageNumber, int pageSize) {

		String sqlCount = "select count(1) as totalCount from (" + preSql + ") as temp";
		String sql = preSql + " limit ?, ? ";
		int offset = pageSize * (pageNumber - 1);
		Object[] params = new Object[] { offset, pageSize };
		return Tuple.apply(sqlCount, sql, params);
	}

	@Override
	public <T> Tuple buildDeleteSQL(T t) {

		DeleteMapper<T> mapper = ModelBuilder.buildDeleteMapper(t);

		if (mapper == null) {
			return null;
		}

		String tableName = mapper.getTableName();
		List<String> pks = mapper.getPrimaryKeyFieldNameList();
		Map<String, String> map = mapper.getJavaToJdbcMapper();

		if (StrKit.isBlank(tableName)) {
			GLog.error("table name is empty~");
			return null;
		}

		if (pks == null || pks.isEmpty()) {
			GLog.error("primary key is empty~");
			return null;
		}

		if (map == null || map.isEmpty()) {
			GLog.error("java to jdbc mapper is empty~");
			return null;
		}

		final String token = " and ";

		// 获取所有字段
		List<Field> fields = ModelBuilder.getObjectAllFieldsWithcache(t.getClass());
		StringBuilder sql = new StringBuilder("delete from " + escape0 + tableName + escape1 + " where ");

		boolean find = false;
		List<Object> params = new ArrayList<Object>();

		try {

			for (Field item : fields) {

				String fieldName = item.getName();

				if (pks.contains(fieldName)) {
					// 字段
					String f = map.get(fieldName);
					item.setAccessible(true);
					Object v = item.get(t);

					if (f != null && v != null) {
						find = true;
						sql.append(escape0).append(f).append(escape1).append(" = ? ").append(token);
						params.add(v);
					}
				}
			}

		} catch (IllegalAccessException e) {
			throw new ExecutingException(e);
		}

		if (!find) {
			return null;
		}

		int totalLen = sql.length();
		int index = sql.length() - token.length();
		sql.delete(index, totalLen);

		Object[] objects = new Object[params.size()];
		for (int i = 0; i < params.size(); ++i) {
			objects[i] = params.get(i);
		}

		String sqlNative = sql.toString();
		GLog.debug("sql:{} , params:{}", sqlNative, objects);

		return Tuple.apply(sqlNative, objects);
	}

	@Override
	public <T> Tuple buildInsertSQL(T t) {

		InsertMapper<T> mapper = ModelBuilder.buildInsertMapper(t);

		if (mapper == null) {
			return null;
		}

		Map<String, String> map = mapper.getJavaToJdbcMapper();
		String tableName = mapper.getTableName();

		if (map == null || map.isEmpty()) {
			GLog.error("insert mapper is empty~");
			return null;
		}

		if (StrKit.isBlank(tableName)) {
			GLog.error("table name is empty~");
			return null;
		}

		// 获取所有字段
		List<Field> fields = ModelBuilder.getObjectAllFieldsWithcache(t.getClass());

		if (fields.isEmpty()) {
			return null;
		}

		StringBuilder fds = new StringBuilder();
		StringBuilder fdsv = new StringBuilder();
		List<Object> params = new ArrayList<Object>();

		fds.append("(");
		fdsv.append("(");

		try {
			for (Field item : fields) {
				String fieldName = item.getName();
				if (map.containsKey(fieldName)) {
					item.setAccessible(true);
					Object v = item.get(t);
					// 值不为空才插入
					if (v != null && !"".equals(v.toString().trim())) {
						String jdbcFieldName = map.get(fieldName);
						fds.append(escape0).append(jdbcFieldName).append(escape1).append(",");
						fdsv.append("?,");
						params.add(v);
					}
				}
			}
		} catch (IllegalAccessException e) {
			throw new ExecutingException(e);
		}

		// 存在插入字段
		if (fds.length() > 1) {

			fds.deleteCharAt(fds.length() - 1);
			fdsv.deleteCharAt(fdsv.length() - 1);
			fds.append(")");
			fdsv.append(")");

		} else {

			return null;
		}

		StringBuilder sb = new StringBuilder();
		sb.append("insert into ").append(escape0).append(tableName).append(escape1).append(fds).append(" values ")
				.append(fdsv);

		Object[] objects = new Object[params.size()];
		for (int i = 0; i < params.size(); ++i) {
			objects[i] = params.get(i);
		}

		String sql = sb.toString();

		GLog.debug("sql:{} , params:{}", sql, objects);
		return Tuple.apply(sql, objects);
	}

	@Override
	public <T> Tuple buildUpdateSQL(T t) {

		UpdateMapper<T> mapper = ModelBuilder.buildUpdateMapper(t);

		if (mapper == null) {
			return null;
		}

		Map<String, String> map = mapper.getJavaToJdbcMapper();
		List<String> pks = mapper.getPrimaryKeyFieldNameList();
		String tableName = mapper.getTableName();

		if (map == null || map.isEmpty()) {
			GLog.error("update mapper is empty~");
			return null;
		}

		if (StrKit.isBlank(tableName)) {
			GLog.error("table name is empty~");
			return null;
		}

		if (pks == null) {
			pks = Collections.emptyList();
		}

		// 获取所有字段
		List<Field> fields = ModelBuilder.getObjectAllFieldsWithcache(t.getClass());

		if (fields.isEmpty()) {
			return null;
		}

		// 主键
		Map<String, Object> pkKv = new HashMap<String, Object>();

		StringBuilder fds = new StringBuilder();
		List<Object> params = new ArrayList<Object>();

		// 获取可设置空字段值
		Set<String> nullableSets = new HashSet<String>();
		if (t instanceof Model) {
			nullableSets = ((Model) t).getNullableFields();
		}

		try {
			for (Field item : fields) {

				item.setAccessible(true);
				String fieldName = item.getName();

				// 判断主键
				if (pks.contains(fieldName)) {

					Object pkValue = item.get(t);
					String columnKey = map.get(fieldName);
					pkKv.put(columnKey, pkValue);

				} else {
					// 字段
					String f = map.get(fieldName);
					Object v = item.get(t);

					if (f != null) {// 找到字段

						boolean nullable = nullableSets.contains(fieldName);// 可为空

						if (v != null) {
							fds.append(escape0).append(f).append(escape1).append(" = ?,");
							params.add(v);
						} else if (nullable) {
							// 可为空，则设置null值
							fds.append(escape0).append(f).append(escape1).append(" = NULL,");
						}

					}

				}
			}
		} catch (IllegalAccessException e) {
			throw new ExecutingException(e);
		}

		// 存在插入字段
		if (fds.length() > 0) {

			fds.deleteCharAt(fds.length() - 1);

		} else {

			throw new RuntimeException("update values is empty ~");
		}

		StringBuilder sb = new StringBuilder();
		sb.append("update ").append(escape0).append(tableName).append(escape1).append(" set ").append(fds);

		if (!pkKv.isEmpty()) {

			final String token = " and ";

			sb.append(" where ");

			if (pks.size() == pkKv.size()) {
				// 全部匹配主键，按顺序组合

				for (String k : pks) {
					String c = map.get(k);
					sb.append(escape0).append(c).append(escape1).append(" = ?").append(token);
					params.add(pkKv.get(c));
				}

			} else {

				for (Entry<String, Object> entry : pkKv.entrySet()) {
					sb.append(escape0).append(entry.getKey()).append(escape1).append(" = ?").append(token);
					params.add(entry.getValue());
				}
			}

			int totalLen = sb.length();
			int index = sb.length() - token.length();
			sb.delete(index, totalLen);
		}

		Object[] objects = new Object[params.size()];
		for (int i = 0; i < params.size(); ++i) {
			objects[i] = params.get(i);
		}

		String sql = sb.toString();
		GLog.debug("sql:{} , params:{}", sql, objects);

		return Tuple.apply(sql, objects);
	}

	@Override
	public Tuple buildDeleteByFieldSQL(List<Pair> kvList, Class<?> clz) {

		ModelFabric mf = ModelBuilder.getModelFabric(clz);

		String table = mf.getTableName();
		if (table == null) {
			throw new ExecutingException("对象没有Table注解");
		}

		Map<String, String> am = mf.getAttrMapping();
		if (am == null) {
			throw new ExecutingException("对象映射字段缺失");
		}

		String andToken = " and ";
		StringBuilder sb = new StringBuilder();
		List<Object> paramList = new ArrayList<>();

		for (int i = 0; i < kvList.size(); ++i) {
			Pair kv = kvList.get(i);
			String field = kv.getKey();
			Object value = kv.getValue();
			String trueField = am.get(field);
			if (trueField == null) {
				trueField = field;
			}
			if (value == null) {
				sb.append(escape0).append(trueField).append(escape1).append(" is null").append(andToken);
			} else {
				sb.append(escape0).append(trueField).append(escape1).append(" = ?").append(andToken);
				paramList.add(value);
			}

		}
		sb.delete(sb.length() - andToken.length(), sb.length());
		String sql = "delete from " + escape0 + table + escape1 + " where " + sb.toString();

		return Tuple.apply(sql, paramList.toArray());
	}

	@Override
	public Tuple buildQueryByFieldSQL(List<Pair> kvList, Class<?> clz) {

		return buildQueryByFieldSQL(kvList, clz, null);
	}

	@Override
	public Tuple buildQueryByFieldSQL(List<Pair> kvList, Class<?> clz, String[] fields) {

		ModelFabric mf = ModelBuilder.getModelFabric(clz);

		String table = mf.getTableName();
		if (table == null) {
			throw new ExecutingException("对象没有Table注解");
		}

		Map<String, String> am = mf.getAttrMapping();
		if (am == null) {
			throw new ExecutingException("对象映射字段缺失");
		}

		String andToken = " and ";
		StringBuilder sb = new StringBuilder();

		List<Object> paramList = new ArrayList<>();

		for (int i = 0; i < kvList.size(); ++i) {
			Pair kv = kvList.get(i);

			String field = kv.getKey();
			Object value = kv.getValue();

			String trueField = am.get(field);
			if (trueField == null) {
				trueField = field;
			}
			if (value == null) {
				sb.append(escape0).append(trueField).append(escape1).append(" is null").append(andToken);
			} else {
				sb.append(escape0).append(trueField).append(escape1).append(" = ?").append(andToken);
				paramList.add(value);
			}

		}
		sb.delete(sb.length() - andToken.length(), sb.length());

		// 选择字段
		StringBuilder sf = new StringBuilder();
		if (fields != null && fields.length > 0) {
			for (String field : fields) {
				String trueField = am.get(field);
				if (trueField == null) {
					trueField = field;
				}
				sf.append(escape0).append(trueField).append(escape1).append(",");
			}
			sf.deleteCharAt(sf.length() - 1);
		} else {
			sf.append("*");
		}

		String sql = "select " + sf.toString() + " from " + escape0 + table + escape1 + " where " + sb.toString();

		return Tuple.apply(sql, paramList.toArray());
	}

	@Override
	public String buildSimpleQuery(Class<?> clz) {
		return buildSimpleQuery(clz, null);
	}

	@Override
	public String buildSimpleQuery(Class<?> clz, String[] fields) {

		ModelFabric mf = ModelBuilder.getModelFabric(clz);
		String table = mf.getTableName();
		if (table == null) {
			throw new RuntimeException("对象没有Table注解");
		}

		Map<String, String> am = mf.getAttrMapping();
		if (fields == null || fields.length == 0 || am == null) {
			return "select * from " + escape0 + table + escape1;
		}

		StringBuilder sb = new StringBuilder("select ");
		for (String field : fields) {
			String trueField = am.get(field);
			if (trueField == null) {
				trueField = field;
			}
			sb.append(escape0).append(trueField).append(escape1).append(",");
		}
		sb.deleteCharAt(sb.length() - 1);
		sb.append(" from ").append(escape0).append(table).append(escape1);
		return sb.toString();
	}

	@Override
	public String buildValidateQuery() {
		return "SELECT 1";
	}

	@Override
	public String getTable(Class<?> clz) {
		return ModelBuilder.getModelTable(clz);
	}

	@Override
	public String getTableColumn(Class<?> clz, String prop) {
		ModelFabric mf = ModelBuilder.getModelFabric(clz);
		Map<String, String> attrMap = mf.getAttrMapping();
		String column = attrMap.get(prop);
		if (column == null) {
			column = prop;
		}
		return column;
	}

	@Override
	public String[] buildCreateTableDDL(Class<?> clz) {
		throw new UnsupportedOperationException("not implement");
	}

	@Override
	public String[] buildModifyTableColumnDDL(Class<?> clz, String prop) {
		throw new UnsupportedOperationException("not implement");
	}

	@Override
	public String[] buildAddTableColumnDDL(Class<?> clz, String prop) {
		throw new UnsupportedOperationException("not implement");
	}

}
